package com.course.dao;

import com.course.entity.CourseSelection;
import com.course.util.DBConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class XueshengCourseSelectionDAO {

    public boolean addCourseSelection(CourseSelection selection) {
        String sql = "INSERT INTO course_selections (student_id, course_id, score, status) VALUES (?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, selection.getStudentId());
            pstmt.setInt(2, selection.getCourseId());
            pstmt.setDouble(3, selection.getScore());
            pstmt.setString(4, selection.getStatus());
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("Error updating course selection with ID: " + selection.getId() + " - " + e.getMessage());
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List<CourseSelection> getCourseSelectionsByStudentId(int studentId) {
        List<CourseSelection> selections = new ArrayList<>();
        String sql = "SELECT id, student_id, course_id, score, status FROM course_selections WHERE student_id=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, studentId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                CourseSelection selection = new CourseSelection();
                selection.setId(rs.getInt("id"));
                selection.setStudentId(rs.getInt("student_id"));
                selection.setCourseId(rs.getInt("course_id"));
                selection.setScore(rs.getDouble("score"));
                selection.setStatus(rs.getString("status"));
                selections.add(selection);
            }
            return selections;
        } catch (SQLException e) {
            e.printStackTrace();
            return selections;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List<CourseSelection> getCourseSelectionsByCourseId(int courseId) {
        List<CourseSelection> selections = new ArrayList<>();
        String sql = "SELECT id, student_id, course_id, score, status FROM course_selections WHERE course_id=? AND status='已选'";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, courseId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                CourseSelection selection = new CourseSelection();
                selection.setId(rs.getInt("id"));
                selection.setStudentId(rs.getInt("student_id"));
                selection.setCourseId(rs.getInt("course_id"));
                selection.setScore(rs.getDouble("score"));
                selection.setStatus(rs.getString("status"));
                selections.add(selection);
            }
            return selections;
        } catch (SQLException e) {
            e.printStackTrace();
            return selections;
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public boolean updateCourseSelection(CourseSelection selection) {
        String sql = "UPDATE course_selections SET student_id=?, course_id=?, score=?, status=? WHERE id=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, selection.getStudentId());
            pstmt.setInt(2, selection.getCourseId());
            pstmt.setDouble(3, selection.getScore());
            pstmt.setString(4, selection.getStatus());
            pstmt.setInt(5, selection.getId());
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("Error updating course selection with ID: " + selection.getId() + " - " + e.getMessage());
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public boolean deleteCourseSelection(int selectionId) {
        String sql = "DELETE FROM course_selections WHERE id=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, selectionId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 删除与指定课程ID相关的所有选课记录
    public boolean deleteSelectionsByCourseId(int courseId) {
        String sql = "DELETE FROM course_selections WHERE course_id=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBConnection.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, courseId);
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0 || affectedRows == 0; // Return true if deletions occurred or no selections existed
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                DBConnection.releaseConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
} 